/***************************************************************
Deletes all the QueryResults older then the @LastTime.  Defaults
to the last 2 days if @LastTime is left null
***************************************************************/

CREATE PROCEDURE dbo.asi_ClearOldQueryResults
    @LastTime datetime = NULL
AS

DECLARE @DeleteTime datetime

SELECT @DeleteTime = CASE WHEN @LastTime IS NULL THEN DateAdd(d, -2, getDate()) ELSE @LastTime END

WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE
    QueryResultItem FROM
    (
        SELECT TOP 500 QueryResultItem.QueryResultKey
            FROM QueryResultItem
            LEFT OUTER JOIN QueryResultMain ON
                QueryResultItem.QueryResultKey = QueryResultMain.QueryResultKey
        WHERE QueryResultMain.CreatedOn < @DeleteTime
    ) q1
    WHERE QueryResultItem.QueryResultKey = q1.QueryResultKey
END

SELECT @DeleteTime = @DeleteTime   --Reset rowcount counter
WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE
    QueryResultMain FROM
    (
        SELECT TOP 500 QueryResultMain.QueryResultKey
            FROM QueryResultMain
        WHERE QueryResultMain.CreatedOn < @DeleteTime
    ) q1
    WHERE QueryResultMain.QueryResultKey = q1.QueryResultKey
END

SELECT @DeleteTime = @DeleteTime --Reset rowcount counter
WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE
    QueryResultItem FROM
    (    
        SELECT TOP 500 QueryResultItem.QueryResultKey
            FROM QueryResultItem
            LEFT OUTER JOIN QueryResultMain ON
                QueryResultItem.QueryResultKey = QueryResultMain.QueryResultKey
        WHERE QueryResultMain.QueryResultKey IS NULL
    ) q1
    WHERE QueryResultItem.QueryResultKey = q1.QueryResultKey
END

GO
GRANT EXECUTE ON  [dbo].[asi_ClearOldQueryResults] TO [IMIS]
GO